package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bilibili.dao.BackDataDao;
import com.bilibili.dao.BaseDao;

public class BackDataDaoImpl implements BackDataDao {

	@Override
	public int selectStatVideo() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int total = 0;
        String sql = "SELECT COUNT(*) AS total_videos FROM video";
        try {
            con = BaseDao.getConn();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getInt("total_videos");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(con, ps, rs);
        }
        return total;
    }

	@Override
	public int selectContent() {
		Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int total = 0;
        String sql = "SELECT COUNT(*) AS total_danmaku FROM danmaku WHERE create_time = CURDATE()";
        try {
            con = BaseDao.getConn();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getInt("total_danmaku");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(con, ps, rs);
        }
        return total;
	}

	@Override
	public int selectVideoPlay() {
		Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int total = 0;
	    String sql = "SELECT COUNT(*) AS count FROM video WHERE status = 0";
	    try {
	        con = BaseDao.getConn();
	        ps = con.prepareStatement(sql);
	        rs = ps.executeQuery();
	        if (rs.next()) {
	        	total = rs.getInt("count");
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    return total;
	}
	@Override
	public int selectAllComment() {
		Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int total = 0;
	    String sql = "SELECT COUNT(*) AS total_comments FROM comment";
	    try {
	        con = BaseDao.getConn();
	        ps = con.prepareStatement(sql);
	        rs = ps.executeQuery();
	        if (rs.next()) {
	        	total = rs.getInt("total_comments");
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    return total;
	}

	@Override
	/**
	 * 查询视频状态分布
	 * @return 包含视频状态分布信息的列表
	 */
	public List<Map<String, Object>> queryVideoStatus() {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    List<Map<String, Object>> resultList = new ArrayList<>();
	    
	    try {
	        // 视频状态分布查询SQL
	        String sql = "SELECT " + 
	                     "    status, " + 
	                     "    COUNT(*) AS video_count, " + 
	                     "    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage " + 
	                     "FROM " + 
	                     "    video " + 
	                     "GROUP BY " + 
	                     "    status " + 
	                     "ORDER BY " + 
	                     "    status";
	        
	        con = BaseDao.getConn();
	        ps = con.prepareStatement(sql);
	        rs = ps.executeQuery();
	        
	        // 处理查询结果
	        while (rs.next()) {
	            Map<String, Object> row = new HashMap<>();
	            row.put("status", rs.getInt("status"));
	            row.put("video_count", rs.getInt("video_count"));
	            row.put("percentage", rs.getDouble("percentage"));
	            resultList.add(row);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return resultList;
	}
	public List<Map<String, Object>> selectVideoTrend(String timeRange) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    List<Map<String, Object>> resultList = new ArrayList<>();
	    System.out.println("查询视频趋势，时间范围: " + timeRange);
	    try {
	        String sql;
	        switch (timeRange) {
	            case "month":
	            	sql = "SELECT " +
	                         "  DATE(create_time) AS date, " +
	                         "  COUNT(*) AS count " +
	                         "FROM " +
	                         "  video " +
	                         "WHERE " +
	                         "  create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) " +
	                         "GROUP BY " +
	                         "  DATE(create_time) " +
	                         "ORDER BY " +
	                         "  date DESC " +
	                         "LIMIT 6";
	                break;
	            case "year":
	            	sql = "SELECT " +
	                         "    DATE_FORMAT(create_time, '%Y-%m') AS date, " +
	                         "    COUNT(*) AS count " +
	                         "FROM " +
	                         "    video " +
	                         "WHERE " +
	                         "    create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
	                         "GROUP BY " +
	                         "    DATE_FORMAT(create_time, '%Y-%m') " +
	                         "ORDER BY " +
	                         "    date DESC " +  // 修改为倒序排序
	                         "LIMIT 6";
	                break;
	            default:
	            	sql = "SELECT " +
	                         "  DATE(create_time) AS date, " +
	                         "  COUNT(*) AS count " +
	                         "FROM " +
	                         "  video " +
	                         "WHERE " +
	                         "  create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) " +
	                         "GROUP BY " +
	                         "  DATE(create_time) " +
	                         "ORDER BY " +
	                         "  date DESC " +
	                         "LIMIT 6";
	        }
	        
	        con = BaseDao.getConn();
	        ps = con.prepareStatement(sql);
	        rs = ps.executeQuery();
	        
	        while (rs.next()) {
	            Map<String, Object> item = new HashMap<>();
	            item.put("date", rs.getString("date"));
	            item.put("count", rs.getInt("count"));
	            resultList.add(item);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return resultList;
	}
	
}
